SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


/** Create Stored Procedures **/
CREATE PROCEDURE dbo.[DnnProduct_CDS_DistributorUserCount]
	@distID varchar(50),
	@userName nvarchar(100)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQLString NVARCHAR(4000)
	DECLARE @ParmDefinition NVARCHAR(512)

	DECLARE @DistID_PID int
	SELECT @DistID_PID = PropertyDefinitionID FROM [DnnProduct_ProfilePropertyDefinition] WHERE PropertyName = 'DistID'

	DECLARE @DistName_PID int
	SELECT @DistName_PID = PropertyDefinitionID FROM [DnnProduct_ProfilePropertyDefinition] WHERE PropertyName = 'DistributorName'

	SET @SQLString = N'SELECT COUNT(U.[UserID]) FROM [DnnProduct_Users] U
	JOIN [DnnProduct_UserProfile] UP1 ON UP1.UserID = U.UserID AND UP1.PropertyDefinitionID = @DistID_PID
	JOIN [DnnProduct_UserProfile] UP2 ON UP2.UserID = U.UserID AND UP2.PropertyDefinitionID = @DistName_PID
	WHERE 1 = 1'
	IF (LEN(@distID) > 0)
	BEGIN
		SET @SQLString = @SQLString + ' AND UP1.[PropertyValue] LIKE @distID'
	END
	IF (LEN(@userName) > 0)
	BEGIN
		SET @SQLString = @SQLString + ' AND U.[Username] LIKE (''%'' + @userName + ''%'')'
	END
		
	SET @ParmDefinition = N'@DistID_PID int, @DistName_PID int, @distID varchar(255), @userName nvarchar(100)'
	EXECUTE sp_executesql @SQLString, @ParmDefinition, @DistID_PID, @DistName_PID, @distID, @userName
END
GO
